import ibm_db_dbi
import pymssql
import pymysql
import pyodbc
import cx_Oracle



class DB():
    def __init__(self):
        self.pool = None
        self.conn = None
        self.cursor = None

    def __del__(self):
        if self.cursor is not None: self.cursor.close()
        if self.conn is not None: self.conn.close()

    def db2_connect(self):
        self.conn = ibm_db_dbi.connect("DATABASE=user;HOSTNAME=0.0.0.0;PORT=50000;PROTOCOL=TCPIP;UID=user;PWD=pw;", user="user", password="pw")
        self.cursor = self.conn.cursor()
        return self

    def fht_connect(self):
        # cx_Oracle.init_oracle_client(lib_dir=r'D:/env/Driver/Oracle/instantclient_21_12')
        # self.conn = cx_Oracle.connect(user="user", password="pw", dsn=cx_Oracle.makedsn(host="0.0.0.0", port="1521", service_name="db") )  # 连接数据库
        self.conn = cx_Oracle.connect(user="user", password="pw", dsn="0.0.0.0:1521/db")  # 连接数据库

        self.cursor = self.conn.cursor()
        return self


    def oracle_east_test_connect(self):
        # cx_Oracle.init_oracle_client(lib_dir=r'D:/env/Driver/Oracle/instantclient_21_12')
        self.conn = cx_Oracle.connect(user="user", password="pw", dsn="0.0.0.0:1521/db")  # 连接数据库

        self.cursor = self.conn.cursor()
        return self


    # def mssql_connect(self):
    #     # 查询不要用中文
    #     self.conn = pymssql.connect(host='0.0.0.0', user='sa', password='pw', database='db', charset='cp936')
    #     self.cursor = self.conn.cursor()
    #     return self

    def mssql_connect(self):
        self.conn = pyodbc.connect('DRIVER={sql server};SERVER=0.0.0.0,1433;DATABASE=db;UID=sa;PWD=pw')
        self.cursor = self.conn.cursor()
        return self

    def mssql_connect3326(self):
        self.conn = pymssql.connect(host='0.0.0.0', user='', password='', database='db_name')
        self.cursor = self.conn.cursor()
        # self.conn = pyodbc.connect('DRIVER={sql server};SERVER=0.0.0.0,1433;DATABASE=;UID=user;PWD=db')
        # self.cursor = self.conn.cursor()
        return self

    def mysql_connect(self):
        # 数据库对象
        self.conn = pymysql.connect(host='0.0.0.0', user='root', passwd='pw', port=3306, db='tk', charset='utf8')
        self.cursor = self.conn.cursor()
        return self

    def get(self, sql, handle=True):
        print(sql)
        if not handle:
            return
        if self.pool is not None:
            self.conn = self.pool.Pool.connection()
            self.cursor = self.conn.cursor()

        self.cursor.execute(sql)
        # <class 'tuple'> (,)
        fetchall = self.cursor.fetchall()
        print(f'query length {len(fetchall)}')
        return fetchall

    def get_new(self, sql):
        print(sql)
        cursor = self.conn.get_connection().cursor().cursor
        cursor.execute(sql)
        # <class 'tuple'> (,)
        return cursor.fetchall()

    def get_export(self, rnt_list):
        '''
        code_list: self.get(sql) - db.get_export(db.get(sql))
        '''
        rnt_title = [tuple([des[0] for des in self.cursor.description])]
        return rnt_title + rnt_list

    def get_one(self, sql):
        self.cursor.execute(sql)
        # <class 'tuple'> ()
        return self.cursor.fetchone()

    def set(self, sql):
        print(sql)
        self.cursor.execute(sql)
        self.conn.commit()
        return self.cursor.rowcount

    def set_new(self, sql):
        print(sql)
        conn = self.conn.get_connection()
        conn.cursor.execute(sql)
        conn.commit()
        return conn.cursor.rowcount

    def set_multi(self, sql, tuples):
        """
        sql: insert into user(id, name) values(?, ?)
        tuples: [tuple(),tuple()] - [(1,'code'), (2, 'b')]
        """

        cnt = self.cursor.executemany(sql, tuples)
        self.conn.commit()
        return cnt

    def set_multi_id_column(self, sql, columns):
        """
        rows: ['code', 'b']
        """
        tuples = [(i, e) for i, e in enumerate(columns)]
        return self.set_multi(sql, tuples)


# def demo_flask(conn, cursor, sql, csv_description=None):
#     cursor.execute(sql)
#
#     csv_head = cursor.rnt_head
#     csv_data = cursor.code_list()
#     return export_2_csv(csv_head, csv_data, csv_description)

db = DB()

if __name__ == '__main__':
    print(db.db2_connect().get_export('SELECT * FROM '))

    print(1)
